import csv
import random

import pymysql
import pandas as pd
from random_id import IdWorker
import numpy as np
import time
from datetime import datetime, timedelta
import string
from faker import Faker

"""
生成一个6位数的合法地区码
"""


def generate_area_code():
    area_code = random.randint(110000, 659005)
    return str(area_code)


"""
生成合法的出身日期
"""


def generate_birthday():
    start_date = datetime.strptime('1970-01-01', '%Y-%m-%d')
    end_date = datetime.now()
    days = (end_date - start_date).days
    birthday = start_date + timedelta(days=random.randint(0, days))
    return birthday.strftime('%Y%m%d')


"""
生成合法的顺序码
"""


def generate_order_code():
    order_code = random.randint(0, 999)
    return str(order_code).zfill(3)


"""
生成校验码
"""


def generate_check_code(id_number):
    factors = [int(x) for x in id_number[:17]]

    weights = [int(x) for x in '7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 '.split()]
    check_code_map = '10X98765432'
    check_code = sum([factor * weight for factor, weight in zip(factors, weights)]) % 11
    return check_code_map[check_code]


"""
生成完整的身份证号
"""


def generate_id_number():
    area_code = generate_area_code()
    birthdate = generate_birthday()
    order_code = generate_order_code()
    id_number = area_code + birthdate + order_code
    check_code = generate_check_code(id_number)
    return id_number + check_code, birthdate


def timestamp_to_datetime(ts):
    """
    时间戳转日期时间
    :param ts: 时间戳
    :return:
    """
    dt = datetime.strptime(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(ts)), "%Y-%m-%d %H:%M:%S")
    return dt


def get_random_area():
    regions = ['北京', '上海', '广州', '深圳', '杭州', '成都', '武汉', '重庆']
    return random.choice(regions)


conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    passwd='root',
    db='recommend',
    port=3306,
    charset="utf8")


# dtype = {"comments_count": np.int32, "article_publish_time": np.int32}
# data = pd.read_csv('../dataset/今日头条新闻文章15425.csv', dtype=dtype)

def generate_password(length):
    characters = string.ascii_letters + string.digits + string.punctuation
    password = ''.join(random.choice(characters) for _ in range(length))
    return password


worker = IdWorker(1, 2, 0)
article_infos = []
article_contents = []
user_profiles = []
user_basics = []
visible = [0, 1]
default = [0, 1]
channel_datas=[]
"""
    插入news_article_basic基础信息表
"""


def init_article(data):
    for index, row in data.iterrows():
        channel_id = random.choice(channel_datas)[0]
        row['article_id'] = worker.get_id()
        if pd.isna(row['comments_count']):
            row['comments_count'] = random.randint(1, 1000)
        # 日期转换，从timestamp 转成 正常日期格式
        row['article_publish_time'] = timestamp_to_datetime(row['article_publish_time'])
        row['update_time'] = row['article_publish_time']
        article_info = (
            str(row['article_id']), str(row['user_id']), str(channel_id), str(row['article_title']), str(row['article_cover']),
            str(row['article_publish_time']), str(row['update_time']), int(row['comments_count']))
        article_infos.append(article_info)
        article_content = (
            str(row['article_id']), str(row['article_content']))
        article_contents.append(article_content)
    _values = "%s,%s,%s, %s, %s, %s,%s, %s"
    _values2 = "%s,%s"
    # 插入news_article_basic表
    sql = """insert into news_article_basic(article_id, user_id, channel_id, title, cover, create_time, update_time, comment_count) values(%s)""" % _values
    sql2 = """insert into news_article_content(article_id, content) values(%s)""" % _values2
    print(sql)
    print(sql2)
    cursor = conn.cursor()
    cursor2 = conn.cursor()
    conn.ping(reconnect=True)
    cursor.executemany(sql, article_infos)
    cursor2.executemany(sql2, article_contents)
    conn.commit()
    conn.close()
    cursor.close()
    cursor2.close()
    print('初始化news_article_basic成功！')


"""
初始化人员画像user_profile，通过Faker包造假数据
"""


def init_user_profile(data):
    # 先对user_id去重 和 article_author生成一个新的DataFrame

    df_new = data.iloc[:, [6, 7]]
    print(len(df_new))
    df_new = pd.DataFrame(df_new)
    df_new = pd.DataFrame(df_new.drop_duplicates(subset=['user_id', 'article_author'], keep=False, inplace=False))
    print(len(df_new))
    for index, row in df_new.iterrows():
        # 用假数据将所有的字段值得到
        gender = random.choice(['1', '0'])
        create_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        update_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        register_media_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        id_number, birthday = generate_id_number()
        area = get_random_area()

        # 从excel循环插入到mysql表中
        user_profile = (
            str(row['user_id']), str(gender), str(birthday), str(row['article_author']), str(create_time),
            str(update_time),
            str(register_media_time),
            str(id_number), str(area))
        user_profiles.append(user_profile)
    _values = "%s,%s, %s, %s, %s,%s, %s,%s, %s"
    sql = """insert into user_profile(user_id, gender, birthday, real_name, create_time, update_time, register_media_time, id_number, area) values(%s)""" % _values
    print(sql)

    cursor = conn.cursor()
    conn.ping(reconnect=True)
    cursor.executemany(sql, user_profiles)
    conn.commit()
    conn.close()
    cursor.close()
    print('初始化user_profiles成功！')


def init_user_basic(data):
    df_new = data.iloc[:, 6]
    set(df_new)
    print(len(df_new))
    df_new = pd.DataFrame(df_new)
    df_new = pd.DataFrame(df_new.drop_duplicates(subset=['user_id'], keep=False, inplace=False))
    print(len(df_new))
    faker = Faker()
    for index, row in df_new.iterrows():
        # 用假数据将所有的字段值得到
        user_name = faker.name()
        password = generate_password(8)
        mobile = faker.phone_number()
        last_login = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        is_media = random.choice(['1', '0'])
        is_verified = '1'
        article_count = random.randint(50, 1000)
        following_count = random.randint(1, 1000)
        fans_count = random.randint(50, 1000)
        read_count = random.randint(10, 10000)
        email = faker.email()

        # 从excel循环插入到mysql表中
        user_basic = (
            str(row['user_id']), str(user_name), str(password), str(mobile), str(last_login), str(is_media),
            str(is_verified),
            str(article_count), str(following_count), str(fans_count), str(read_count), str(email))
        user_basics.append(user_basic)
    _values = "%s,%s, %s, %s, %s,%s, %s,%s, %s, %s, %s, %s"
    sql = """insert into user_basic(user_id, user_name, password, mobile, last_login, is_media, is_verified, article_count,
    following_count, fans_count, read_count, email) values(%s)""" % _values
    print(sql)
    conn.ping(reconnect=True)
    cursor = conn.cursor()
    cursor.executemany(sql, user_basics)
    conn.commit()
    conn.close()
    cursor.close()
    print('初始化user_basics成功！')


def init_channel(datas):
    for data in datas:
        channel_id = worker.get_id()
        channel_name = data
        update_time = datetime.now()
        create_time = datetime.now()
        sequence = random.randint(1, 1000)
        is_visible = random.choice(visible)
        is_default = random.choice(default)
        channel_data = (channel_id, channel_name, create_time, update_time, sequence, is_visible, is_default)
        channel_datas.append(channel_data)
    _values = "%s,%s,%s,%s,%s,%s,%s"
    sql = """insert into news_channel(channel_id, channel_name, create_time, update_time, sequence, is_visible, is_default) values(%s) """ % _values
    print(sql)
    cursor = conn.cursor()
    cursor.executemany(sql, channel_datas)
    conn.commit()
    cursor.close()
    print("初始化news_channel表成功！")


if __name__ == '__main__':
    # read csv
    data = pd.read_csv('../dataset/今日头条新闻文章15425.csv', encoding='UTF-8')
    data = data.dropna()
    print("开始处理数据：", len(data))
    # 初始化频道表news_channel
    with open('../dataset/channel.txt', 'r', encoding='UTF-8') as file:
        datas = file.read().splitlines()

    init_channel(datas)
    # 初始化文章内容表 news_article_basic 和 news_article_content 和 news_channel表
    init_article(data)

    # 初始化用户表数据
    init_user_profile(data)
    init_user_basic(data)

    print(data.shape)
